﻿using Dapper;
using liaowa2.Entity.DataModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace liaowa2.Dao.Manager
{
    public class NavigationDao
    {
        #region 生成代码

        public static bool Exists(string where)
        {
            using (IDbConnection conn = SqlConn.OpenConnectionStar())
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select count(1) from Navigation where ");
                strSql.Append(where);

                var model = conn.Execute(strSql.ToString(), null, commandType: CommandType.Text);

                return model > 0 ? true : false;
            }
        }



        /// <summary>
        /// 增加一条数据,返回ID
        /// </summary>
        public static int Add(Navigation model)
        {
            DepperSqlHelp<Navigation> help = new DepperSqlHelp<Navigation>();
            int result = help.InsertReturn(model, "Navigation");
            return result;
        }


        /// <summary>
        /// 更新一条数据
        /// </summary>
        public static bool Update(Navigation model)
        {
            DepperSqlHelp<Navigation> help = new DepperSqlHelp<Navigation>();
            bool result = help.Update(model, "Navigation", " where id=" + model.id);
            return result;
        }


        /// <summary>
        /// 删除一条数据
        /// </summary>
        public static bool Delete(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from Navigation ");
            strSql.Append(" where id = @id ");

            var p = new DynamicParameters();
            p.Add("@id", id);

            using (IDbConnection conn = SqlConn.OpenConnectionStar())
            {
                var result = conn.Query<int>(strSql.ToString(), p, commandType: CommandType.Text).FirstOrDefault();
                if (result > 0)
                {
                    return true;
                }
                return true;
            }
        }

        /// <summary>
        /// 批量删除一批数据
        /// </summary>
        public static bool DeleteList(string idlist)
        {
            var ids = idlist.Split(',');
            int result = 0;

            var p = new DynamicParameters();
            p.Add("@IntID", idlist);

            using (IDbConnection conn = SqlConn.OpenConnectionStar())
            {
                string sql = " delete Navigation where id in (" + string.Join(",", ids) + "); ";
                result = conn.Query<int>(sql, p, commandType: CommandType.Text).FirstOrDefault();
            }
            if (result > 0)
            {
                return true;
            }
            return true;
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public static Navigation GetModel(int id)
        {
            var p = new DynamicParameters();
            p.Add("@Id", id);

            using (IDbConnection conn = SqlConn.OpenConnectionStar())
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select * ");
                strSql.Append(" FROM Navigation");
                strSql.Append(" where id=@Id");

                var model = conn.Query<Navigation>(strSql.ToString(), p, commandType: CommandType.Text).FirstOrDefault();
                return model;
            }
        }


        /// <summary>
        /// 获得数据列表
        /// </summary>
        public static List<Navigation> GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM Navigation ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }

            using (IDbConnection conn = SqlConn.OpenConnectionStar())
            {
                var model = conn.Query<Navigation>(strSql.ToString(), null, commandType: CommandType.Text).ToList();
                return model;
            }
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public static List<Navigation> GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" * ");
            strSql.Append(" FROM Navigation ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);

            using (IDbConnection conn = SqlConn.OpenConnectionStar())
            {
                var model = conn.Query<Navigation>(strSql.ToString(), null, commandType: CommandType.Text).ToList();
                return model;
            }
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public static int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM Navigation ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            using (IDbConnection conn = SqlConn.OpenConnectionStar())
            {
                var model = conn.Query<int>(strSql.ToString(), null, commandType: CommandType.Text).FirstOrDefault();
                return model;
            }
        }

        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public static List<Navigation> GetListByPage(string strWhere, string orderby, int startIndex, int endIndex, ref int count)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.Rid desc");
            }
            strSql.Append(")AS Row, T.*  from Navigation T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            using (IDbConnection conn = SqlConn.OpenConnectionStar())
            {
                var model = conn.Query<Navigation>(strSql.ToString(), null, commandType: CommandType.Text).ToList();
                return model;
            }
        }
        
        #endregion
    }
}
